/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package vzone.bank;

import java.util.ArrayList;
import java.util.UUID;
import java.util.logging.Level;
import java.util.logging.Logger;
import vzone.structs.BasePeople;
import vzone.structs.Billing;
import vzone.structs.OperatorCode;
import vzone.tools.Date;
import vzone.tools.MD5;
import vzone.tools.SqlHelper;

/**
 *Bank类实现
 * 
 * @author Kingpro
 */
public class Bank implements IBank
{

    private static final String CREATEACCOUNT = "INSERT INTO [b_bankAccount]([accountId], [peopleId], [createDate], [balance], [pwdHash]) VALUES(?,?,?,?,?);";
    private static final String GETBALANCE = "SELECT [balance] FROM [b_bankAccount] WHERE [accountId]=? AND [pwdHash]=?";
    private static final String TRANSFER = "UPDATE [b_bankAccount] SET [balance]=[balance] - ? WHERE [accountId]=? AND [pwdHash]=?; " +
	    "UPDATE [b_bankAccount] SET [balance]=[balance]+ ? WHERE [accountId]=?;" +
	    "INSERT INTO [b_bankBilling] VALUES(?, ?, ?, ?, ?,?);";
    private static final String CHECKACCOUNTEXISTS = "SELECT [accountId] FROM [b_bankAccount] WHERE [accountId]=?";
    private static final String CHECKINBILLING = "SELECT * FROM [b_bankBilling] WHERE [billingId]=? AND [toAccountId]=? AND EXISTS(SELECT [accountId] FROM [b_bankAccount] WHERE [accountId]=? AND [pwdHash]=?)";
    private static final String CHECKOUTBILLING = "SELECT * FROM [b_bankBilling] WHERE [billingId]=? AND [fromAccountId]=? AND EXISTS(SELECT [accountId] FROM [b_bankAccount] WHERE [accountId]=? AND [pwdHash]=?)";
    private static final String LISTBILLLING = "SELECT * FROM [b_bankBilling] WHERE ([fromAccountId]=? OR [toAccountId]=?) AND EXISTS(SELECT [accountId] FROM [b_bankAccount] WHERE [accountId]=? AND [pwdHash]=?)";
    private static final String ACCOUNTLIST = "SELECT [accountId] FROM [b_bankAccount] WHERE [peopleId]=?";
    /**
     * 静态Bank类实例
     */
    private static Bank bank = new Bank();

    /**
     * 获取静态对象Bank
     * 
     * @return bank
     */
    public static Bank getBank()
    {
	return bank;
    }

    /**
     * OK
     * @param people
     * @param passwordHashS
     * @return
     * @throws Exception
     */
    @Override
    public UUID CreateAccount(BasePeople people, String passwordHashS) throws Exception
    {
	UUID accountIdUuid = UUID.randomUUID();
	SqlHelper.ExecuteNonQuery(
		SqlHelper.getConnect(),
		CREATEACCOUNT,
		new Object[]
		{
		    accountIdUuid.toString(),
		    people.PeopleIdUuid.toString(),
		    (new Date()).getTime(),
		    0,
		    MD5.getMD5(passwordHashS)
		});
	return accountIdUuid;
    }

    @Override
    public OperatorCode CancelAccount(UUID accountIdUuid, String passwordHashS)
    {
	throw new UnsupportedOperationException("Not supported yet.");
    }

    @Override
    public OperatorCode ChangePassword(UUID accountIdUuid, String oldPwdS, String newPwdS)
    {
	throw new UnsupportedOperationException("Not supported yet.");
    }

    @Override
    public OperatorCode GetAccountPeople(UUID accountId, String pwd, BasePeople people)
    {
	throw new UnsupportedOperationException("Not supported yet.");
    }

    /**
     * OK
     * @param billing
     * @param fromAccountPwdS
     * @return
     */
    @Override
    public OperatorCode Transfer(Billing billing, String fromAccountPwdS)
    {
	vzone.tools.Double balanceEx = new vzone.tools.Double(0);
	if (GetAccountBalance(billing.FromAccount, fromAccountPwdS, balanceEx) != OperatorCode.SUCCESS)
	{
	    return OperatorCode.NOACCOUNT;
	}
	if (balanceEx.doubleValue() < billing.Balance)
	{
	    return OperatorCode.OVERFRAFT;
	}
	if (CheckAccountExists(billing.ToAccount) != OperatorCode.SUCCESS)
	{
	    return OperatorCode.NOACCOUNT;
	}
	billing.BillingIDUuid = UUID.randomUUID();
	billing.AccuredTimeD = new Date();
	try
	{
	    SqlHelper.ExecuteNonQuery(SqlHelper.getConnect(), TRANSFER, new Object[]
		    {
			billing.Balance,
			billing.FromAccount.toString(),
			MD5.getMD5(fromAccountPwdS),
			billing.Balance,
			billing.ToAccount.toString(),
			billing.BillingIDUuid.toString(),
			billing.FromAccount.toString(),
			billing.ToAccount.toString(),
			billing.Balance,
			billing.AccuredTimeD.getTime(),
			billing.DescriptionS
		    });
	    return OperatorCode.SUCCESS;
	}
	catch (Exception ex)
	{
	    return OperatorCode.SQLERROR;
	}
    }

    @Override
    public OperatorCode GetLoan(double balanceD, UUID accountUuid, String pwdHashS)
    {
	throw new UnsupportedOperationException("Not supported yet.");
    }

    /**
     *
     * @param accountIDUuid
     * @param pwdHashS
     * @param balance
     * @return
     */
    @Override
    public OperatorCode GetAccountBalance(UUID accountIDUuid, String pwdHashS, vzone.tools.Double balance)
    {
	try
	{
	    Object obj = SqlHelper.ExecuteScalar(SqlHelper.getConnect(), GETBALANCE, "balance", new Object[]
		    {
			accountIDUuid.toString(), MD5.getMD5(pwdHashS)
		    });
	    if (obj != null)
	    {
		balance.setDouble(Double.parseDouble(obj.toString()));
		return OperatorCode.SUCCESS;
	    }
	    return OperatorCode.OTHER_ERROR;
	}
	catch (Exception ex)
	{
	    return OperatorCode.SQLERROR;
	}
    }

    /**
     * OK
     * @param billing
     * @param pwdHashS
     * @return
     */
    @Override
    public OperatorCode CheckInBilling(Billing billing, String pwdHashS)
    {
	try
	{
	    ArrayList<Object> result = SqlHelper.ExecuteReader(
		    SqlHelper.getConnect(),
		    CHECKINBILLING,
		    new Object[]
		    {
			billing.BillingIDUuid.toString(),
			billing.ToAccount.toString(),
			billing.ToAccount.toString(),
			MD5.getMD5(pwdHashS)
		    });
	    if (result.size() == 0)
	    {
		return OperatorCode.NOBILLING;
	    }
	    Object[] resultbilling = (Object[]) result.get(0);
	    billing.FromAccount = UUID.fromString(resultbilling[1].toString());
	    billing.Balance = Double.parseDouble(resultbilling[3].toString());
	    Date date = new Date();
	    date.setTime(Long.parseLong(resultbilling[4].toString()));
	    billing.AccuredTimeD = date;
	    billing.DescriptionS = resultbilling[5].toString();
	    return OperatorCode.SUCCESS;
	}
	catch (Exception ex)
	{
	    return OperatorCode.SQLERROR;
	}
    }

    /**
     * OK
     * @param billing
     * @param pwdHashS
     * @return
     */
    @Override
    public OperatorCode CheckOutBilling(Billing billing, String pwdHashS)
    {
	try
	{
	    ArrayList<Object> result = SqlHelper.ExecuteReader(
		    SqlHelper.getConnect(),
		    CHECKOUTBILLING,
		    new Object[]
		    {
			billing.BillingIDUuid.toString(),
			billing.FromAccount.toString(),
			billing.FromAccount.toString(),
			MD5.getMD5(pwdHashS)
		    });
	    if (result.size() == 0)
	    {
		return OperatorCode.NOBILLING;
	    }
	    Object[] resultbilling = (Object[]) result.get(0);
	    billing.ToAccount = UUID.fromString(resultbilling[2].toString());
	    billing.Balance = Double.parseDouble(resultbilling[3].toString());
	    Date date = new Date();
	    date.setTime(Long.parseLong(resultbilling[4].toString()));
	    billing.AccuredTimeD = date;
	    billing.DescriptionS = resultbilling[5].toString();
	    return OperatorCode.SUCCESS;
	}
	catch (Exception ex)
	{
	    return OperatorCode.SQLERROR;
	}
    }

    /**
     * OK
     * @param accountIDUuid
     * @param pwdHashS
     * @param lists
     * @return
     */
    @Override
    public OperatorCode ListBilling(UUID accountIDUuid, String pwdHashS, ArrayList<Billing> lists)
    {
	try
	{
	    ArrayList<Object> resultAl = SqlHelper.ExecuteReader(SqlHelper.getConnect(), LISTBILLLING, new Object[]
		    {
			accountIDUuid.toString(), accountIDUuid.toString(), accountIDUuid.toString(), MD5.getMD5(pwdHashS)
		    });
	    for (int i = 0; i < resultAl.size(); i++)
	    {
		Object[] objects = (Object[]) resultAl.get(i);
		Billing billing = new Billing();
		billing.BillingIDUuid = UUID.fromString(objects[0].toString());
		billing.FromAccount = UUID.fromString(objects[1].toString());
		billing.ToAccount = UUID.fromString(objects[2].toString());
		billing.Balance = Double.parseDouble(objects[3].toString());
		Date date = new Date();
		date.setTime(Long.parseLong(objects[4].toString()));
		billing.AccuredTimeD = date;
		billing.DescriptionS = objects[5].toString();
		lists.add(billing);
	    }
	    return OperatorCode.SUCCESS;
	}
	catch (Exception ex)
	{
	    return OperatorCode.SQLERROR;
	}
    }

    /**
     * OK
     * @param accountId
     * @return
     */
    public OperatorCode CheckAccountExists(UUID accountId)
    {
	try
	{
	    Object object = SqlHelper.ExecuteScalar(SqlHelper.getConnect(), CHECKACCOUNTEXISTS, "accountId", new Object[]
		    {
			accountId.toString()
		    });
	    if (object == null)
	    {
		return OperatorCode.NOACCOUNT;
	    }
	    return OperatorCode.SUCCESS;
	}
	catch (Exception ex)
	{
	    return OperatorCode.OTHER_ERROR;
	}

    }

    public OperatorCode GetAccountList(UUID peopleIdUuid, ArrayList<String> list)
    {
	try
	{
	    ArrayList<Object> lists = SqlHelper.ExecuteReader(SqlHelper.getConnect(), ACCOUNTLIST, new Object[]
		    {
			peopleIdUuid.toString()
		    });
	    for (int i = 0; i < lists.size(); i++)
	    {
		Object[] objects = (Object[]) lists.get(i);
		list.add(objects[0].toString());
	    }
	    return OperatorCode.SUCCESS;
	}
	catch (Exception ex)
	{
	    return OperatorCode.OTHER_ERROR;
	}

    }
}
